//using System;
//using System.Collections.Generic;
//using System.Data;
//using System.Data.SqlClient;
//using System.Text;

//namespace ClientTools.Library.Versions
//{
//    public class VersionManager : BaseManager
//    {
//        public VersionManager(string connectionString)
//            : base(connectionString)
//        { }

//        //public VersionInfo DeleteVersionsByDate(DateTime date)
//        //{
//        //    // initialize the returning object
//        //    VersionInfo returnValue = new VersionInfo();

//        //    // initialize and open the Connection
//        //    System.Data.SqlClient.SqlConnection con = this.GetConnection();

//        //    // first load all content elements
//        //    DataSet dsContent = new DataSet();
//        //    SqlDataAdapter daContent = new SqlDataAdapter("SELECT * FROM cmsContent", con);
//        //    daContent.Fill(dsContent);

//        //    // and all versions
//        //    SqlDataAdapter daContentVersion = new SqlDataAdapter("SELECT * FROM cmsContentVersion", con);
//        //    DataSet dsContentVersion = new DataSet();
//        //    daContentVersion.Fill(dsContentVersion);

//        //    // and all documents
//        //    SqlDataAdapter daDocuments = new SqlDataAdapter("SELECT * FROM cmsDocument", con);
//        //    DataSet dsDocuments = new DataSet();
//        //    daDocuments.Fill(dsDocuments);

//        //    // for each content
//        //    foreach (DataRow drContent in dsContent.Tables[0].Rows)
//        //    {
//        //        int nodeId = (int)drContent["nodeID"];
//        //        // get the versions of this content element sorted descending by VersionDate 
//        //        DataRow[] drContentVersion = dsContentVersion.Tables[0].Select(string.Concat("ContentId = ", nodeId), "VersionDate DESC");
//        //        // set the variable if a published version is found
//        //        bool bFoundFirstPublished = false;
//        //        // for each version
//        //        for (int i = 0; i < drContentVersion.Length; i++)
//        //        {
//        //            Guid version = (Guid)drContentVersion[i]["versionID"];
//        //            // get the document of this version
//        //            DataRow[] drDocuments = dsDocuments.Tables[0].Select(string.Concat("nodeID = ", nodeId, " AND versionID = '", version, "'"));

//        //            // if there is no document leave this version in and go on with the loop
//        //            if ((drDocuments == null) || drDocuments.Length == 0)
//        //            {
//        //                continue;
//        //            }
//        //            DataRow drDocument = drDocuments[0];

//        //            // if the published version isn't found yet
//        //            if (!bFoundFirstPublished)
//        //            {
//        //                // check if the actual version is published
//        //                if ((bool)drDocument["published"])
//        //                {
//        //                    // if it is published leave this version in and set the variable that a published version is found
//        //                    bFoundFirstPublished = true;
//        //                }
//        //                // and go on with the loop
//        //                continue;
//        //            }
//        //            // if we got here then check if the VersionDate is lower than the given date
//        //            if (((DateTime)drContentVersion[i]["VersionDate"] < date) && (!(bool)drDocument["published"]))
//        //            {
//        //                // if so then delete all property data, the document and the version itself and increment the counters for the return infos
//        //                returnValue.PropertyData += new SqlCommand("DELETE FROM cmsPropertyData WHERE VersionId = '" + version.ToString() + "'", con).ExecuteNonQuery();
//        //                returnValue.Documents += new SqlCommand("DELETE FROM cmsDocument WHERE VersionId = '" + version.ToString() + "'", con).ExecuteNonQuery();
//        //                returnValue.Versions += new SqlCommand("DELETE FROM cmsContentVersion WHERE VersionId = '" + version.ToString() + "'", con).ExecuteNonQuery();
//        //            }
//        //        }
//        //        returnValue.Content++;
//        //    }
//        //    return returnValue;
//        //}

//        //public VersionInfo DeleteVersionsByDate(DateTime date, int id, bool recursive)
//        //{
//        //    // initialize the returning object
//        //    VersionInfo returnValue = new VersionInfo();

//        //    if (recursive)


//        //    // initialize and open the Connection
//        //    System.Data.SqlClient.SqlConnection con = this.GetConnection();

//        //    StringBuilder sb1 = new StringBuilder();
//        //    sb1.Append("SELECT * FROM cmsDocument WHERE VersionID IN (");
//        //    sb1.Append("SELECT VersionId FROM cmsContentVersion WHERE ContentId = {0} AND VersionDate < (");
//        //    sb1.Append("	SELECT cmsContentVersion.VersionDate FROM cmsDocument ");
//        //    sb1.Append("		INNER JOIN cmsContentVersion on cmsDocument.VersionId = cmsContentVersion.versionId");
//        //    sb1.Append("	WHERE nodeId = {0} AND published = 1)");
//        //    sb1.Append(")");

//        //    StringBuilder sb2 = new StringBuilder();
//        //    sb2.Append("SELECT * FROM cmsContentVersion WHERE VersionID IN (");
//        //    sb2.Append("SELECT VersionId FROM cmsContentVersion WHERE ContentId = {0} AND VersionDate < (");
//        //    sb2.Append("	SELECT cmsContentVersion.VersionDate FROM cmsDocument ");
//        //    sb2.Append("		INNER JOIN cmsContentVersion on cmsDocument.VersionId = cmsContentVersion.versionId");
//        //    sb2.Append("	WHERE nodeId = {0} AND published = 1)");
//        //    sb2.Append(")");

//        //    StringBuilder sb3 = new StringBuilder();
//        //    sb3.Append("SELECT * FROM cmsPropertyData WHERE VersionID IN (");
//        //    sb3.Append("SELECT VersionId FROM cmsContentVersion WHERE ContentId = {0} AND VersionDate < (");
//        //    sb3.Append("SELECT cmsContentVersion.VersionDate FROM cmsDocument ");
//        //    sb3.Append("INNER JOIN cmsContentVersion on cmsDocument.VersionId = cmsContentVersion.versionId");
//        //    sb3.Append("WHERE nodeId = {0} AND published = 1)");
//        //    sb3.Append(")");

//        //    // first load all content elements
//        //    DataSet dsContent = new DataSet();
//        //    SqlDataAdapter daContent = new SqlDataAdapter("SELECT * FROM cmsContent WHERE ", con);
//        //    daContent.Fill(dsContent);

//        //    // and all versions
//        //    SqlDataAdapter daContentVersion = new SqlDataAdapter("SELECT * FROM cmsContentVersion WHERE ", con);
//        //    DataSet dsContentVersion = new DataSet();
//        //    daContentVersion.Fill(dsContentVersion);

//        //    // and all documents
//        //    SqlDataAdapter daDocuments = new SqlDataAdapter("SELECT * FROM cmsDocument", con);
//        //    DataSet dsDocuments = new DataSet();
//        //    daDocuments.Fill(dsDocuments);

//        //    // for each content
//        //    foreach (DataRow drContent in dsContent.Tables[0].Rows)
//        //    {
//        //        int nodeId = (int)drContent["nodeID"];
//        //        // get the versions of this content element sorted descending by VersionDate 
//        //        DataRow[] drContentVersion = dsContentVersion.Tables[0].Select(string.Concat("ContentId = ", nodeId), "VersionDate DESC");
//        //        // set the variable if a published version is found
//        //        bool bFoundFirstPublished = false;
//        //        // for each version
//        //        for (int i = 0; i < drContentVersion.Length; i++)
//        //        {
//        //            Guid version = (Guid)drContentVersion[i]["versionID"];
//        //            // get the document of this version
//        //            DataRow[] drDocuments = dsDocuments.Tables[0].Select(string.Concat("nodeID = ", nodeId, " AND versionID = '", version, "'"));

//        //            // if there is no document leave this version in and go on with the loop
//        //            if ((drDocuments == null) || drDocuments.Length == 0)
//        //            {
//        //                continue;
//        //            }
//        //            DataRow drDocument = drDocuments[0];

//        //            // if the published version isn't found yet
//        //            if (!bFoundFirstPublished)
//        //            {
//        //                // check if the actual version is published
//        //                if ((bool)drDocument["published"])
//        //                {
//        //                    // if it is published leave this version in and set the variable that a published version is found
//        //                    bFoundFirstPublished = true;
//        //                }
//        //                // and go on with the loop
//        //                continue;
//        //            }
//        //            // if we got here then check if the VersionDate is lower than the given date
//        //            if (((DateTime)drContentVersion[i]["VersionDate"] < date) && (!(bool)drDocument["published"]))
//        //            {
//        //                // if so then delete all property data, the document and the version itself and increment the counters for the return infos
//        //                returnValue.PropertyData += new SqlCommand("DELETE FROM cmsPropertyData WHERE VersionId = '" + version.ToString() + "'", con).ExecuteNonQuery();
//        //                returnValue.Documents += new SqlCommand("DELETE FROM cmsDocument WHERE VersionId = '" + version.ToString() + "'", con).ExecuteNonQuery();
//        //                returnValue.Versions += new SqlCommand("DELETE FROM cmsContentVersion WHERE VersionId = '" + version.ToString() + "'", con).ExecuteNonQuery();
//        //            }
//        //        }
//        //        returnValue.Content++;
//        //    }
//        //    return returnValue;
//        //}

//        //public VersionInfo DeleteVersionsByNumberOfItems(int numberOfItems)
//        //{
//        //    // initialize the returning object
//        //    VersionInfo returnValue = new VersionInfo();

//        //    // initialize and open the Connection
//        //    System.Data.SqlClient.SqlConnection con = this.GetConnection();

//        //    // first load all content elements
//        //    DataSet dsContent = new DataSet();
//        //    SqlDataAdapter daContent = new SqlDataAdapter("SELECT * FROM cmsContent", con);
//        //    daContent.Fill(dsContent);

//        //    // and all versions
//        //    SqlDataAdapter daContentVersion = new SqlDataAdapter("SELECT * FROM cmsContentVersion", con);
//        //    DataSet dsContentVersion = new DataSet();
//        //    daContentVersion.Fill(dsContentVersion);

//        //    // and all documents
//        //    SqlDataAdapter daDocuments = new SqlDataAdapter("SELECT * FROM cmsDocument", con);
//        //    DataSet dsDocuments = new DataSet();
//        //    daDocuments.Fill(dsDocuments);

//        //    // for each content
//        //    foreach (DataRow drContent in dsContent.Tables[0].Rows)
//        //    {
//        //        int nodeId = (int)drContent["nodeID"];
//        //        int versionsLeave = 0;
//        //        // get the versions of this content element sorted descending by VersionDate 
//        //        DataRow[] drContentVersion = dsContentVersion.Tables[0].Select(string.Concat("ContentId = ", nodeId), "VersionDate DESC");
//        //        // set the variable if a published version is found
//        //        bool bFoundFirstPublished = false;
//        //        // for each version
//        //        for (int i = 0; i < drContentVersion.Length; i++)
//        //        {
//        //            Guid version = (Guid)drContentVersion[i]["versionID"];
//        //            // get the document of this version
//        //            DataRow[] drDocuments = dsDocuments.Tables[0].Select(string.Concat("nodeID = ", nodeId, " AND versionID = '", version, "'"));

//        //            // if there is no document leave this version in and go on with the loop
//        //            if ((drDocuments == null) || drDocuments.Length == 0)
//        //            {
//        //                continue;
//        //            }
//        //            DataRow drDocument = drDocuments[0];

//        //            // if the published version isn't found yet
//        //            if (!bFoundFirstPublished)
//        //            {
//        //                // check if the actual version is published
//        //                if ((bool)drDocument["published"])
//        //                {
//        //                    // if it is published leave this version in and set the variable that a published version is found
//        //                    bFoundFirstPublished = true;
//        //                    versionsLeave++;
//        //                }
//        //                // and go on with the loop
//        //                continue;
//        //            }
//        //            // if we got here then check if the number of items to leave is reached
//        //            if ((versionsLeave > numberOfItems) && (!(bool)drDocument["published"]))
//        //            {
//        //                // if so then delete all property data, the document and the version itself and increment the counters for the return infos
//        //                returnValue.PropertyData += new SqlCommand("DELETE FROM cmsPropertyData WHERE VersionId = '" + version.ToString() + "'", con).ExecuteNonQuery();
//        //                returnValue.Documents += new SqlCommand("DELETE FROM cmsDocument WHERE VersionId = '" + version.ToString() + "'", con).ExecuteNonQuery();
//        //                returnValue.Versions += new SqlCommand("DELETE FROM cmsContentVersion WHERE VersionId = '" + version.ToString() + "'", con).ExecuteNonQuery();
//        //            }
//        //            // increment the version counter
//        //            versionsLeave++;
//        //        }
//        //        returnValue.Content++;
//        //    }
//        //    return returnValue;
//        //}

//        #region Delete Versions

//        private const string CDeletePropertyData = "DELETE FROM cmsPropertyData WHERE VersionId = '{0}'";
//        private const string CDeleteDocuments = "DELETE FROM cmsDocument WHERE VersionId = '{0}'";
//        private const string CDeleteVersion = "DELETE FROM cmsContentVersion WHERE VersionId = '{0}'";

//        /// <summary>
//        /// Deletes the given version and returns the informations how many rows have been deleted
//        /// </summary>
//        /// <param name="guid"></param>
//        /// <returns></returns>
//        public VersionInfo DeleteVersion(Guid guid)
//        {
//            // initialize the returning object
//            VersionInfo returnValue = new VersionInfo();

//            // initialize and open the Connection
//            System.Data.SqlClient.SqlConnection con = this.GetConnection();

//            // deletes the given version
//            returnValue.PropertyData += new SqlCommand(string.Format(CDeletePropertyData, guid), con).ExecuteNonQuery();
//            returnValue.Documents += new SqlCommand(string.Format(CDeleteDocuments, guid), con).ExecuteNonQuery();
//            returnValue.Versions += new SqlCommand(string.Format(CDeleteVersion, guid), con).ExecuteNonQuery();

//            return returnValue;
//        }

//        #endregion

//        #region Get Versions

//        /// <summary>
//        /// Gets all versions from the given node
//        /// </summary>
//        /// <param name="id"></param>
//        /// <returns></returns>
//        public VersionCollection GetVersions(int nodeId)
//        {
//            VersionCollection returnValue = new VersionCollection();
//            SqlConnection con = this.GetConnection();

//            System.Data.SqlClient.SqlCommand com
//                = new System.Data.SqlClient.SqlCommand(string.Format("SELECT nodeId, updateDate, cmsDocument.versionId, newest, published, [text], documentUser, userName, VersionDate FROM cmsDocument INNER JOIN umbracoUser ON cmsDocument.documentUser = umbracoUser.id INNER JOIN cmsContentVersion ON cmsDocument.versionId = cmsContentVersion.VersionId WHERE nodeId = {0} ORDER BY versionDate DESC", nodeId), con);
//            System.Data.SqlClient.SqlDataReader dr = com.ExecuteReader();
//            while (dr.Read())
//            {
//                Version v = new Version();
//                v.Id = dr.GetInt32(0);
//                v.UpdateDate = dr.GetDateTime(1);
//                v.Guid = dr.GetGuid(2);
//                v.IsNewest = dr.GetBoolean(3);
//                v.Published = dr.GetBoolean(4);
//                v.Text = dr.GetString(5);
//                v.UserId = dr.GetInt32(6);
//                v.UserName = dr.GetString(7);
//                v.VersionDate = dr.GetDateTime(8);
//                returnValue.Add(v);
//            }

//            return returnValue;
//        }

//        #endregion

//        #region Get Version Infos

//        /// <summary>
//        /// Gets the version informations of the given node. If includeChildren is set it adds the informations of the child nodes
//        /// </summary>
//        /// <param name="nodeId"></param>
//        /// <param name="includeChildren"></param>
//        /// <returns></returns>
//        public VersionInfo GetVersionInfos(int nodeId, bool includeChildren)
//        {
//            VersionInfo returnValue = this.GetVersionInfos(nodeId);
//            if (includeChildren)
//            {
//                VersionInfo childrenInfos = this.GetChildrenVersionInfos(nodeId);
//                returnValue.Documents += childrenInfos.Documents;
//                returnValue.Versions += childrenInfos.Versions;
//                returnValue.PropertyData += childrenInfos.PropertyData;
//            }
//            return returnValue;
//        }

//        /// <summary>
//        /// Gets the version informations from the given node
//        /// </summary>
//        /// <param name="nodeId"></param>
//        /// <returns></returns>
//        public VersionInfo GetVersionInfos(int nodeId)
//        {
//            VersionInfo returnValue = new VersionInfo();
//            SqlConnection con = this.GetConnection();

//            string sql = "SELECT COUNT(*) FROM cmsDocument WHERE nodeId = {0}";
//            System.Data.SqlClient.SqlCommand com = new System.Data.SqlClient.SqlCommand(string.Format(sql, nodeId), con);
//            returnValue.Versions = (int)com.ExecuteScalar();

//            sql = "SELECT COUNT(*) FROM cmsPropertyData WHERE contentNodeId = {0}";
//            com = new System.Data.SqlClient.SqlCommand(string.Format(sql, nodeId), con);
//            returnValue.PropertyData = (int)com.ExecuteScalar();

//            return returnValue;
//        }

//        /// <summary>
//        /// Gets the version informations from all children of the given node
//        /// </summary>
//        /// <param name="id"></param>
//        /// <returns></returns>
//        public VersionInfo GetChildrenVersionInfos(int nodeId)
//        {
//            VersionInfo returnValue = new VersionInfo();
//            SqlConnection con = this.GetConnection();

//            string sql = "SELECT COUNT(*) FROM cmsDocument WHERE nodeID IN (SELECT id FROM umbracoNode WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972' AND [path] LIKE '%,{0}%' AND id <> {0}) AND published = 1";
//            SqlCommand com = new System.Data.SqlClient.SqlCommand(string.Format(sql, nodeId), con);
//            returnValue.Documents = (int)com.ExecuteScalar();

//            sql = "SELECT COUNT(*) FROM cmsDocument WHERE nodeId in (SELECT nodeId FROM cmsDocument WHERE nodeID IN (SELECT id FROM umbracoNode WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972' AND [path] LIKE '%,{0}%' AND id <> {0}) AND published = 1)";
//            com = new SqlCommand(string.Format(sql, nodeId), con);
//            returnValue.Versions = (int)com.ExecuteScalar();

//            sql = "SELECT COUNT(*) FROM cmsPropertyData WHERE contentNodeId in (SELECT nodeId FROM cmsDocument WHERE nodeID IN (SELECT id FROM umbracoNode WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972' AND [path] LIKE '%,{0}%' AND id <> {0}) AND published = 1)";
//            com = new System.Data.SqlClient.SqlCommand(string.Format(sql, nodeId), con);
//            returnValue.PropertyData = (int)com.ExecuteScalar();

//            return returnValue;
//        }

//        /// <summary>
//        /// Gets the version informations from all published nodes
//        /// </summary>
//        /// <param name="id"></param>
//        /// <returns></returns>
//        public VersionInfo GetVersionInfos()
//        {
//            VersionInfo returnValue = new VersionInfo();
//            SqlConnection con = this.GetConnection();

//            SqlCommand com = new SqlCommand("SELECT COUNT(*) FROM cmsDocument WHERE nodeID IN (SELECT id FROM umbracoNode WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972') AND published = 1", con);
//            returnValue.Documents = (int)com.ExecuteScalar();

//            com = new SqlCommand("SELECT COUNT(*) FROM cmsDocument WHERE nodeId in (SELECT nodeId FROM cmsDocument WHERE nodeID IN (SELECT id FROM umbracoNode WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972') AND published = 1)", con);
//            returnValue.Versions = (int)com.ExecuteScalar();

//            com = new SqlCommand("SELECT COUNT(*) FROM cmsPropertyData WHERE contentNodeId in (SELECT nodeId FROM cmsDocument WHERE nodeID IN (SELECT id FROM umbracoNode WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972') AND published = 1)", con);
//            returnValue.PropertyData = (int)com.ExecuteScalar();

//            return returnValue;
//        }

//        #endregion
//    }
//}
